<?php
/**
 * Created by PhpStorm.
 * User: 吕金鹏
 * Date: 2021/6/10
 * Time: 3:15 下午
 */

namespace app\index\controller;
use app\index\controller\Base;
use think\facade\Request;
use think\db;
use app\index\model\createWord;
class Center extends Base
{
    public function shenhe(){
        if($this->admin['rid']!=11){
            echo '你没该有权限';die;
        }
        $renzheng = Db::name("renzheng")->where("if_del",0)->paginate(10)->each(function($item){
            // $item['nian'] = 0;
            // $pwork = json_decode($item['pwork'],true);
            // // [{"t_stime":"2009-06-10","t_etime":"2021-06-10"}]
            // foreach($pwork as $key=> $val){
            //     $item['nian'] += strtotime($val["t_etime"]) - strtotime($val["t_stime"]);
            // }
            // $item['nian'] = Sec2Time($item['nian']);
            return $item;
        });
        // dump($renzheng);die;
        $this->assign('list',$renzheng);
        $this->assign('page',$renzheng->render());
        return $this->fetch();
    }
    public function memberinfo(){
        $id = input('id');

        $member = Db::name("renzheng")->where("id",$id)->find();

        $pwork = json_decode($member['pwork'],true);
        $this->assign('pwork',$pwork);
        $work = json_decode($member['work'],true);

        $this->assign('work',$work);
        $peixun = json_decode($member['peixun'],true);

        $this->assign('peixun',$peixun);

        $this->assign('member',$member);
        $area = Db::name('cate')->where('status',1)->where('pid',0)->select();
        $this->assign('area',$area);
        $area = Db::name("cate")->where('id',$member['gid'])->find();
        $qu = Db::name('cate')->where("id",$area['pid'])->find();
        $city = Db::name("cate")->where('id',$qu['pid'])->find();
        $area_list = Db::name("cate")->where('pid',0)->select();
        $this->assign('area',$area);
        $this->assign('qu',$qu);
        $this->assign('city',$city);
        $this->assign('area_list',$area_list);

        return $this->fetch();
    }
    public function changestatus(){
        $id = input('id');
        if(is_array($id)){
            $id_str ='';
            foreach($id as $key=>$val){
                if($key==0){
                    $id_str = $val;
                }else{
                    $id_str .=','.$val;
                }
            }
            $res = Db::name('renzheng')->where("id",'in',$id_str)->update(['status'=>6]);
        }else{
            $res = Db::name("renzheng")->where('id',$id)->update(['status'=>6]);
        }
        ajaxMsg('1','提交成功');
    }
    public function nochangestatus(){
        $id = input('id');
        $msg = input('msg');
        $res = Db::name("renzheng")->where('id',$id)->update(['status'=>7,'center_message'=>$msg]);
        ajaxMsg('1','提交成功');
    }
    public function ketishenqing(){

        $id = input("id");
        $nian = input("nian");
        if(!$nian){
            $list = Db::name("keti")->select();
            $ary =[];
            foreach($list as $val){
                $ary[] = $val['year'];
            }
            $ary = array_unique($ary);

            $this->assign("nian",$ary);
            return $this->fetch("chosenian");
        }
        if($id==''){
            $list = Db::name("keti")->where("year",$nian)->select();
            $this->assign("list",$list);
            $this->assign('nian',$nian);
            return $this->fetch("step1");
        }
        $cate = input('cate');
            $ids = '';
            if($cate){
                
                $id_ary = Db::name('cate')->where("pid",'in',$cate)->select(); 
                foreach($id_ary as $key=>$val){
                        if($key==0){
                            $ids .= $val['id'];
                        }else{
                            $ids .=','.$val['id'];
                        }
                }
                if($cate==68){
                   
                    $id_ary = Db::name('cate')->where("pid",'in',$ids)->select(); 
                    $ids ='';
                    foreach($id_ary as $key=>$val){
                        if($key==0){
                            $ids .= $val['id'];
                        }else{
                            $ids .=','.$val['id'];
                        }
                    }
                }
            }
         
        $if_inportant = input("if_inportant");
        $this->assign('if_inportant',$if_inportant);
        $school = input("school");
        $this->assign('id',$id);
        $where = [];
        $where['sq.cid'] = $id;
        $where1 = [];
        $where1[] = ['sq.status','>',1];
        $str = '';
         
        if($if_inportant){
            $where['if_inportant'] = $if_inportant;
        }
        if($school){
            $school = Db::name("cate")->whereLike('title','%'.$school.'%')->select();
           
            if($school){
                foreach($school as $key=>$val){
                    if($key==0){
                        $str=$val['id'];
                    }else{
                        $str.=','.$val['id'];
                    }
                }
            }
        }
        $cate = input('cate');
        $this->assign('cate',$cate);
            $ids = '';
            if($cate){
                
                $id_ary = Db::name('cate')->where("pid",'in',$cate)->select(); 
                foreach($id_ary as $key=>$val){
                        if($key==0){
                            $ids .= $val['id'];
                        }else{
                            $ids .=','.$val['id'];
                        }
                }
                if($cate==68){
                   
                    $id_ary = Db::name('cate')->where("pid",'in',$ids)->select(); 
                    $ids ='';
                    foreach($id_ary as $key=>$val){
                        if($key==0){
                            $ids .= $val['id'];
                        }else{
                            $ids .=','.$val['id'];
                        }
                    }
                }
            }
            if($ids){
                $str = $ids;
            }
        $order = input('order');
        $order_str = '';
        if(!$order){
            $order_str = 'sq.c_fen desc';
        }else{
            $order_str = 'sq.c_fen '.$order;
        }
        if($str){
            $list = Db::name("shenqing as sq")
                    ->leftjoin('keti kt','kt.id=sq.cid')
                    ->leftjoin("admin ad",'ad.id=sq.admin_id')
                    ->where($where)
                    ->where($where1)
                    ->where("school",'in',$str)
                    // ->leftjoin("cate c",'c.id=a.school')
                    // ->where("ad.school",$this->admin['school'])
                    // ->where("ad.area","in",[2,52])
                    //1 学校待审核 2 学校拒绝 3 中心待审核 4 教委待审核 5 教委拒绝 6 审核通过 7 中心审核拒绝 
                    ->where('sq.status','not in','0,1,2,4,5')
                    ->order($order_str)
                    ->field("kt.name,sq.count1,sq.count2,sq.message,sq.status,sq.id,sq.time,sq.if_re,sq.if_two,sq.content,ad.name as relname,sq.school_id,sq.if_inportant,sq.c_fen as fen")
                    ->paginate(10,false,['query'=>request()->param()]);
        }else{
            $list = Db::name("shenqing as sq")
                    ->leftjoin('keti kt','kt.id=sq.cid')
                    ->leftjoin("admin ad",'ad.id=sq.admin_id')
                    ->where($where)
                    ->where($where1)
                    // ->leftjoin("cate c",'c.id=a.school')
                    // ->where("ad.school",$this->admin['school'])
                    // ->where("ad.area","in",[2,52])
                    //1 学校待审核 2 学校拒绝 3 中心待审核 4 教委待审核 5 教委拒绝 6 审核通过 7 中心审核拒绝 
                    ->where('sq.status','in','3,6,7')
                    ->order($order_str)
                    ->field("kt.name,sq.count1,sq.count2,sq.message,sq.status,sq.id,sq.time,sq.if_re,sq.if_two,sq.content,ad.name as relname,sq.school_id,sq.if_inportant,sq.c_fen as fen")
                    ->paginate(10,false,['query'=>request()->param()]);
        }        
        $this->assign('page',$list->render());
        //根据学校种类.生成审核部门
        $title = [];
  
        //查询学校分类
 
        $title[1] = '市中心审核';
      
        $this->assign('title',$title);
        $list = $list->toArray();
        $new_ary = [];
  
        foreach($list['data'] as $key=>$val){
            $new_ary[$key] = $val;
            $new_ary[$key]['content'] = json_decode($val['content'],true);

            $new_ary[$key]['ctitle'] = Db::name("cate")->where('id',$val['school_id'])->find()['title'];
            foreach($title as $k=>$v){
            
                $new_ary[$key]['statusdes'][$k] = getstatus($v,$val['status']);

            }
            // echo 123;die;
            //查看打分的数量
            $count1 = Db::name("dafen as df")
                    ->join('admin a','df.admin_id=a.id')
                    // ->where('a.school','')
                    ->where('a.cid',0)
                    ->where("sid",$val['id'])
                    ->count();
            //查看学校所属类型
            $school_type = Db::name("cate")->where("id",$val['school_id'])->find();
            if($school_type['pid']==2||$school_type['pid']==52){
                $ftype=1;
            }else{
                $ftype=2;
            }
            //查看可以打分专家数量
            $count2 = Db::name("admin")->where('cid',0)->where("rid",12)->where("ftype",$ftype)->where("z_type",'in',['全部课题',$val['if_inportant']])->count();
            // echo $count1;die;
            if($count1==$count2){
                $new_ary[$key]['shenhe'] = 1;
            }else{
                $new_ary[$key]['shenhe'] = 0;
            }                        
        }
        $this->assign("admin_data",$this->admin);
        $this->assign('id',$id);
        $this->assign('nian',$nian);
        $this->assign('id',$id);
        $this->assign('data',$new_ary);
        return $this->fetch();
    }
 
    public function chakan(){
        $id = input('id');
        $type = input('type');
        $info = Db::name('shenqing')->where('id',$id)->find();
        $this->assign("info",$info);
        $content = json_decode($info['content'],true);
        $content['s_name'] = isset($content['s_name'])?array_values($content['s_name']):'';
        $content['s_num'] =isset($content['s_num'])?array_values($content['s_num']):'';
        $content['s_price'] = isset($content['s_price'])?array_values($content['s_price']):'';
        $content['s_year'] = isset($content['s_year'])?array_values($content['s_year']):'';
        $content['member_name'] = isset($content['member_name'])?array_values($content['member_name']):'';
        $content['member_zhiye'] = isset($content['member_zhiye'])?array_values($content['member_zhiye']):'';
        $content['member_year'] = isset($content['member_year'])?array_values($content['member_year']):'';
        $content['member_zhuanye'] = isset($content['member_zhuanye'])?array_values($content['member_zhuanye']):'';
        $content['member_danwei'] = isset($content['member_danwei'])?array_values($content['member_danwei']):'';
        $content['member_fengong'] = isset($content['member_fengong'])?array_values($content['member_fengong']):'';
        $content['cate_title'] = isset($content['cate_title'])?array_values($content['cate_title']):'';
        $content['class_title'] = isset($content['class_title'])?array_values($content['class_title']):'';
        $content['p_time'] = isset($content['p_time'])?array_values($content['p_time']):'';
        $content['if_finish'] = isset($content['if_finish'])?array_values($content['if_finish']):'';
        $content['sannianchengguo'] = isset($content['sannianchengguo'])?$content['sannianchengguo']:'';
        // dump($content);die;
        $this->assign('content',$content);
        $this->assign("if_two",$info['if_two']);
        
        if($type==1){
            //查看打分详情
        $dafen_list = Db::name('admin')
                        // ->Leftjoin('dafen df','a.id=df.admin_id')
                        // ->where("df.sid",$info['id'])
                        ->where("cid",0)
                        ->where('rid',12)
                        ->where('z_type','in',[$info['if_inportant'],'全部课题'])
                        // ->field("df.*,a.name")
                        ->select();
            foreach($dafen_list as $key=>$val){
                $if_exit = Db::name('dafen')->where('sid',$info['id'])->where('admin_id',$val['id'])->find();
                if($if_exit){
                    $dafen_list[$key]['fen'] = $if_exit['fen'];
                }else{
                    $dafen_list[$key]['fen'] = 0;
                }
            }   
        $this->assign("dafen",$dafen_list);
            return $this->fetch('school/kanfen');
        }
        return $this->fetch();
        
    }
    public function agreen(){
        $id = input("id");
        
        if(Request::isPost()){
            $data = input("post.");
            $sq = Db::name("shenqing")->where("id",$id)->find();
            // dump($data);die;
            $info['message'] = $sq['message']."<br/> 中心意见:".$data['message'];
            // $info['count2'] = $data['count'];
            $info['status'] = 6;
            // //增加审核通过人数
            // $school_info = Db::name('admin')->where('id',$sq['admin_id'])->find();
            
            // if($school_info['area']==52){
            //     $cate = "直属校";

            //     $member_class = Db::name("member_class")->where("cid",$sq['cid'])->where("type",$cate)->find();
            
            //      $count = DB::name("shenqing")->where("school_id",$school_info['school'])->where('cid',$sq['cid'])->where('status',6)->count();
                 
            //     if($member_class['num']==$count){
            //         $count = DB::name("shenqing")->where("school_id",$school_info['school'])->where('cid',$sq['cid'])->where('status','<>',6)->update(['status'=>7]);
            //     }
            // }elseif($school_info['area']==2){
            //     $cate = "普通高校、高职院校";
            //     $member_class = Db::name("member_class")->where("cid",$sq['cid'])->where("type",$cate)->find();
            
            //     $count = DB::name("shenqing")->where("school_id",$school_info['school'])->where('cid',$sq['cid'])->where('status',6)->count();
            //     if($member_class['num']==$count){
            //         $count = DB::name("shenqing")->where("school_id",$school_info['school'])->where('cid',$sq['cid'])->where('status','<>',6)->update(['status'=>7]);
            //     }

            // }else{
            //     $cate = "其他中小学/中职/特教学校";

            //     $member_class = Db::name("member_class")->where("cid",$sq['cid'])->where("type",$cate)->find();
            
            //     $count = DB::name("shenqing")->where("area",$school_info['area'])->where('cid',$sq['cid'])->where('status',6)->count();
            //     if($member_class['num']==$count){
            //         $count = DB::name("shenqing")->where("area",$school_info['area'])->where('cid',$sq['cid'])->where('status','<>',6)->update(['status'=>7]);
            //     }
            // }
          
            
            
            // Db::name("member_class")->where("type",$school_info['school_type'])->where("cid",$sq['cid'])->setInc("anum");
            $res = Db::name("shenqing")->where("id",$data['id'])->update($info);
            if($res){
                ajaxMsg(1,'审核成功');
            }
            ajaxMsg(0,'网络错误,审核失败');
        }
        $this->assign("id",$id);
        return $this->fetch();
    }
    public function disagreen(){
        $id = input("id");
        
        if(Request::isPost()){
            $data = input("post.");
            $sq = Db::name("shenqing")->where("id",$id)->find();
            $info['message'] = $sq['message']."<br/>中心意见:".$data['message'];
            $info['if_re'] = $data['if_re'];
            $info['status'] = 7;
            $res = Db::name("shenqing")->where("id",$data['id'])->update($info);
            if($res){
                ajaxMsg(1,'拒绝成功');
            }
            ajaxMsg(0,'网络错误,审核失败');
        }
        $this->assign("id",$id);
        return $this->fetch();
    }
    public function addexcel(){
        $cate = input("cate");
        $if_inportant = input("if_inportant");
        $cid = input("cid");
        $ids = '';
        // dump($cate);die;
         if($cate){
                
                $id_ary = Db::name('cate')->where("pid",'in',$cate)->select(); 
                foreach($id_ary as $key=>$val){
                        if($key==0){
                            $ids .= $val['id'];
                        }else{
                            $ids .=','.$val['id'];
                        }
                }
                if($cate==68){
                    $id_ary = Db::name('cate')->where("pid",'in',$ids)->select(); 
                    $ids ='';
                    foreach($id_ary as $key=>$val){
                        if($key==0){
                            $ids .= $val['id'];
                        }else{
                            $ids .=','.$val['id'];
                        }
                    }
                }
            }
        $where = [] ;
        // echo $ids;die;
        if($ids){
            $where[] = ['school','in',$ids];
        }
        if($if_inportant){
            $where[] = ['if_inportant','=',$if_inportant];
        }
        $where[] = ['sq.status','in','3,6,7'];
        // dump($where);die;
        $list = Db::name("shenqing as sq")
                    ->leftjoin('keti kt','kt.id=sq.cid')
                    ->leftjoin("admin ad",'ad.id=sq.admin_id')
                    ->where($where)
                    ->leftjoin("cate c",'c.id=sq.school_id')
                    // ->where("ad.school",$this->admin['school'])
                    // ->where("ad.area","in",[2,52])
                    //1 学校待审核 2 学校拒绝 3 中心待审核 4 教委待审核 5 教委拒绝 6 审核通过 7 中心审核拒绝 
                    ->where('sq.status','in','3,6,7')
                    ->order('id asc')
                    ->field("kt.name,sq.count1,sq.count2,sq.message,sq.status,sq.id,sq.time,sq.if_re,sq.if_two,sq.content,ad.name as relname,sq.school_id,sq.if_inportant,sq.c_fen as fen,c.title as school_title,ad.phone as relphone")
                    ->select();
        // dump($list);die;
        $keti = Db::name("keti")->where("id",$cid)->find();
     
        require"../vendor/PHPExcel/PHPExcel.php";
         $objPHPExcel = new \PHPExcel();
        //4.激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //5.设置表格头（即excel表格的第一行）
        if($cate==68){
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '所属区')
                ->setCellValue('B1', '授权负责人姓名')
                ->setCellValue('C1', '授权负责人电话')
                ->setCellValue('D1', '申报老师姓名')
                ->setCellValue('E1', '申报老师电话')
                ->setCellValue('F1', '申报课题ID号')
                ->setCellValue('G1', '申报课题名称')
                ->setCellValue('H1', '申报老师所属学校');
        }else{
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '学校名称')
                ->setCellValue('B1', '授权负责人姓名')
                ->setCellValue('C1', '授权负责人电话')
                ->setCellValue('D1', '申报老师姓名')
                ->setCellValue('E1', '申报老师电话')
                ->setCellValue('F1', '申报课题ID号')
                ->setCellValue('G1', '申报课题名称');
        }
        
        //设置A列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
                    ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置单元格宽度
        //6.循环刚取出来的数组，将数据逐一添加到excel表格。
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30);
        if($cate==68){  
            for($i=0;$i<count($list);$i++){
                $list[$i]['content'] = json_decode($list[$i]['content'],true);
                
                $pid = Db::name("cate")->where('id',$list[$i]['school_id'])->field("pid")->find()['pid'];
                $area_title = Db::name("cate")->where("id",$pid)->find()['title'];
                $area_admin = Db::name("admin")->where("area",$pid)->where("rid",9)->find();
                
                $objPHPExcel->getActiveSheet()->setCellValue('A'.($i+2),$area_title);//ID
                $objPHPExcel->getActiveSheet()->setCellValue('B'.($i+2),$area_admin['name']);//标签码
                $objPHPExcel->getActiveSheet()->setCellValue('C'.($i+2),$area_admin['phone']);//防伪码
                $objPHPExcel->getActiveSheet()->setCellValue('D'.($i+2),$list[$i]['relname']);//ID
                $objPHPExcel->getActiveSheet()->setCellValue('E'.($i+2),$list[$i]['relphone']);//标签码
                $objPHPExcel->getActiveSheet()->setCellValue('F'.($i+2),$list[$i]['id']);//ID
                $objPHPExcel->getActiveSheet()->setCellValue('G'.($i+2),$list[$i]['content']['title']);//标签码
                $objPHPExcel->getActiveSheet()->setCellValue('H'.($i+2),$list[$i]['school_title']);//防伪码
            }
        }else{
            for($i=0;$i<count($list);$i++){
                $list[$i]['content'] = json_decode($list[$i]['content'],true);
                $area_admin = Db::name("admin")->where("school",$list[$i]['school_id'])->where("rid",8)->find();
                
                $objPHPExcel->getActiveSheet()->setCellValue('A'.($i+2),$list[$i]['school_title']);//ID
                $objPHPExcel->getActiveSheet()->setCellValue('B'.($i+2),$area_admin['name']);//标签码
                $objPHPExcel->getActiveSheet()->setCellValue('C'.($i+2),$area_admin['phone']);//防伪码
                $objPHPExcel->getActiveSheet()->setCellValue('D'.($i+2),$list[$i]['relname']);//ID
                $objPHPExcel->getActiveSheet()->setCellValue('E'.($i+2),$list[$i]['relphone']);//标签码
                $objPHPExcel->getActiveSheet()->setCellValue('F'.($i+2),$list[$i]['id']);//ID
                $objPHPExcel->getActiveSheet()->setCellValue('G'.($i+2),$list[$i]['content']['title']);//标签码
                // $objPHPExcel->getActiveSheet()->setCellValue('H'.($i+2),$list[$i]['stu_profess']);//防伪码
            }
        }
        
        //7.设置保存的Excel表格名称
        $filename = $keti['name'].'课题申请人员.xls';
        //8.设置当前激活的sheet表格名称；
        $objPHPExcel->getActiveSheet()->setTitle('人员名单');
        //9.设置浏览器窗口下载表格
        header("Content-Type: application/force-download"); 
        header("Content-Type: application/octet-stream"); 
        header("Content-Type: application/download"); 
        header('Content-Disposition:inline;filename="'.$filename.'"'); 
        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        $objWriter->save('php://output');
        exit;
    }
    public function zhengshu(){
        $list = Db::name("zhengshu")->select();
        
        $this->assign("data",$list);
        return $this->fetch();
    }
    public function addzs(){
        if(Request::isPost()){
            $data = input("post.");
            $data['time'] = time();
            $data['status'] = 1;
            $res = Db::name("zhengshu")->insert($data);
            if($res){
                return json(['code'=>1,'msg'=>'新增成功']);
            }
            return json(['code'=>0,'msg'=>'网络错误']);
        }
        return $this->fetch();
    }
    public function delzs(){
        $id = input('id');
        $res = Db::name("zhengshu")->where("id",$id)->delete();
        if($res){
            return json(['code'=>1,'msg'=>'删除成功']);
        }
        return json(['code'=>0,'msg'=>'网络延时，稍后再试']);
    }
    public function changezsStatus(){
        $id = input('id');
        $status = input("status");
        $status = $status=='false'? 0 :1;
        $res = Db::name("zhengshu")->where("id",$id)->update(['status'=>$status]);
        if($res){
            return json(['status'=>1,'msg'=>'修改成功']);
        }
        return json(['status'=>0,'msg'=>'无效变更']);
    }
}